﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WINBASE.Utils.DBHelper;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace WINBASE.Utils.NETHelper
{
    class NetHandler
    {
        string connectionstring = "";
        public NetHandler(string ConnectionString)
        {
            connectionstring = ConnectionString;
        }

        public string getBO(string TableName,Language Lang)
        {
            string strBO = "";
            string strDirective = "";
            string strNamespace = "";
            string strClass = "";
            string strProperty = "";
            string strCode = "";
            string strAgrument = "";
            string strConstructor = "";
            string strConstructCode = "";
            string strParam = "";
            
            DBSchema myDbSchema = new DBSchema(connectionstring);
            ColumnCollection myColumns = myDbSchema.getColDef(TableName);

            string strClassName = TableName + "BO";

            int cntCol = 0;
            foreach (Column myCol in myColumns)
            {
                cntCol++;
                switch (Lang)
                {
                    case Language.CS:
                        strConstructCode += (strConstructCode == "" ? "\t\t\t" : "\n\t\t\t") + "this." + myCol.Name + " = " + myCol.Name + ';' ;
                        strParam += (strParam == "" ? "" : ",\n\t\t\t\t\t\t\t\t  ") + myCol.Name;
                        strAgrument += (strAgrument == "" ? "" : ", ") + ((cntCol % 4) == 0 ? "\n\t\t\t\t" + getSpacing(TableName + "BO") : "") + getParamString(myCol, Lang);
                        break;
                    case Language.VB:
                        strConstructCode += "\n\t\tMe." + myCol.Name + " = " + myCol.Name;
                        strParam += (strParam == "" ? "" : ", ") + ((cntCol % 4) == 0 ? "\n\t\t\t\t\t\t\t " : "") + myCol.Name;
                        strAgrument += (strAgrument == "" ? "" : ", ") + ((cntCol % 4) == 0 ? "\n\t\t\t\t   " : "") + getParamString(myCol, Lang);
                        break;
                    default: break;
                }
                strProperty += getProperty(myCol, Lang) + (cntCol != myColumns.Count ? "\n\n" : ""); 
            }

            strConstructor += getConstructor(strClassName, "", Lang, "") + "\n\n" +
                              getConstructor(strClassName, strAgrument, Lang, strConstructCode) + "\n\n";

            strCode += strConstructor + strProperty;

            switch (Lang)
            {
                case Language.CS:
                    strCode += "\n\n\t\tpublic object[] ToObjectArray()" + "\n" +
                               "\t\t{\n" +
                               "\t\t\t" + "return new object[] { " + strParam + " }\n" +
                               "\t\t}";
                    break;
                case Language.VB:
                    strCode += "\n\n\tPublic Function ToObjectArray() As Object()" + "\n" +
                               "\t\t" + "Return New Object() {" + strParam + "}" + "\n" +
                               "\tEnd Function";
                    break;
                default: break;
            }

            strClass = getClass(strClassName, Lang, strCode);

            strDirective = getDirective(Lang) + "\n\n";
            strNamespace = getNameSpace(myDbSchema.DatabaseName,Lang,strClass);

            strBO = strDirective + strNamespace;
            return strBO;
        }

        public string getDAL(string TableName,Language Lang)
        {
            string strDAL = "";
            string strDirective = "";
            string strNamespace = "";
            string strClass = "";
            string strCode = "";

            DBSchema myDbSchema = new DBSchema(connectionstring);
            ColumnCollection myColumns = myDbSchema.getColDef(TableName);

            string strParam = "";
            string strCondition = "";
            string strArgument = "";
            foreach (Column myCol in myColumns)
            {
                if (myCol.InPrimaryKey)
                {
                    strParam += (strParam == "" ? "" : ",") + getParamString(myCol, Lang);
                    strCondition += (strCondition == "" ? "" : " AND ") + getCondition(myCol, Lang);
                    strArgument += (strArgument == "" ? "" : ", ") + myCol.Name;
                }
            }

            switch (Lang)
            {
                case Language.CS:
                    strCode += "\t\t" + "public object SelectSingle(" + strParam + ", String ColumnName)" + "\n" +
                              "\t\t{\n" +
                              "\t\t\t" + "return Program.dbHandler.GetAVal(" + '"' + "SELECT " + '"' + " + ColumnName + " + '"' + " FROM " + TableName + " WHERE " + strCondition + '"' + ", " + strArgument + ");\n" +
                              "\t\t}\n\n";

                    strCode += "\t\t" + "public DataRow SelectRow(" + strParam + ")" + "\n" +
                               "\t\t{\n" +
                               "\t\t\t" + "return Program.dbHandler.GetARow(" + '"' + "SELECT * FROM " + TableName + " WHERE " + strCondition + '"' + ", " + strArgument + ");\n" +
                               "\t\t}\n\n";

                    strCode += "\t\t" + "public DataTable SelectTable(string range, List<object> rangeargs, List<object> @orderby)" + "\n" +
                               "\t\t{\n" + 
                               "\t\t\t" + "return Program.dbHandler.GetADataTable(" + '"' + TableName + '"' + ", range, rangeargs, @orderby);" + "\n" +
                               "\t\t}\n\n";

                    strCode += "\t\t" + "public int Insert(" + strParam + ')' + "\n" +
                               "\t\t{\n" + 
                               "\t\t\t" + "return Program.dbHandler.ExecProc(" + '"' + "spi_" + TableName + '"' + ", " + strArgument + ");" + "\n" +
                               "\t\t}\n\n";

                    strCode += "\t\t" + "public int Update(" + strParam + ')' + "\n" +
                               "\t\t{\n" +
                               "\t\t\t" + "return Program.dbHandler.ExecProc(" + '"' + "spu_" + TableName + '"' + ", " + strArgument + ");" + "\n" +
                               "\t\t}\n\n";

                    strCode += "\t\t" + "public int Delete(" + strParam + ')' + "\n" +
                               "\t\t{\n" +
                               "\t\t\t" + "return Program.dbHandler.ExecProc(" + '"' + "spd_" + TableName + '"' + ", " + strArgument + ");" + "\n" +
                               "\t\t}";
                    break;
                case Language.VB:
                    strCode += "\t" + "Public Function SelectSingle(" + strParam + ", ColumnName As String) As Object " + "\n" +
                               "\t\t" + "Return Program.dbHandler.GetAVal(" + '"' + "SELECT " + '"' + " & " + "ColumnName" + " & " + '"' +  " FROM " + TableName + " WHERE " + strCondition + '"' + ", " + strArgument +")" + "\n" +
                               "\t" + "End Function\n\n";

                    strCode += "\t" + "Public Function SelectRow(" + strParam + ") As DataRow" + "\n" +
                               "\t\t" + "Return Program.dbHandler.GetARow(" + '"' + "SELECT * FROM " + TableName + " WHERE " + strCondition + '"' + ", " + strArgument + ")\n" +
                               "\t" + "End Function\n\n";

                    strCode += "\t" + "Public Function SelectTable(range As String, rangeargs As List(Of Object), orderby As List(Of Object)) As DataTable" + "\n" +
                               "\t\t" + "Return Program.dbHandler.GetADataTable(" + '"' + TableName + '"' +", range, rangeargs, orderby)" + "\n" +
                               "\t" + "End Function\n\n";

                    strCode += "\t" + "Public Function Insert(" + strParam + ") As Integer" + "\n" +
                               "\t\t" + "Return Program.dbHandler.ExecProc(" + '"' + "spi_" + TableName + '"' + ", " + strArgument + ")" + "\n" +
                               "\t" + "End Function\n\n";

                    strCode += "\t" + "Public Function Update(" + strParam + ") As Integer" + "\n" +
                               "\t\t" + "Return Program.dbHandler.ExecProc(" + '"' + "spu_" + TableName + '"' + ", " + strArgument + ")" + "\n" +
                               "\t" + "End Function\n\n";

                    strCode += "\t" + "Public Function Delete(" + strParam + ") As Integer" + "\n" +
                               "\t\t" + "Return Program.dbHandler.ExecProc(" + '"' + "spd_" + TableName + '"' + ", " + strArgument + ")" + "\n" +
                               "\t" + "End Function";
                    break;
                default: break;
            }
            
            strClass = getClass(TableName + "DAL", Lang, strCode);

            strDirective = getDirective(Lang) + "\n\n";
            strNamespace = getNameSpace(myDbSchema.DatabaseName, Lang, strClass);

            strDAL = strDirective + strNamespace;
            return strDAL;
        }

        public string getBLL(string TableName, Language Lang)
        {
            string strBLL = "";
            string strDirective = "";
            string strNamespace = "";
            string strClass = "";
            string strCode = "";
            string strAgrument = "";
            string strParam1 = "";
            string strParam2 = "";

            DBSchema myDbSchema = new DBSchema(connectionstring);
            ColumnCollection myColumns = myDbSchema.getColDef(TableName);

            int cntCol1 = 1;
            int cntCol2 = 0;
            foreach (Column myCol in myColumns)
            {
                if (myCol.InPrimaryKey)
                {
                    strParam1 += (strParam1 == "" ? "" : ", ") + ((cntCol1 % 4) == 0 ? "\n\t\t\t\t\t\t\t " : "") + myCol.Name;
                    strAgrument += (strAgrument == "" ? "" : ", ") + ((cntCol1 % 4) == 0 ? "\n\t\t\t\t   " : "") + getParamString(myCol, Lang);
                    cntCol1++;
                }
                switch (Lang)
                {
                    case Language.CS:
                        strParam2 += (strParam2 == "" ? "" : ",\n\t\t\t\t\t\t" + getSpacing(TableName + "BO")) + "my" + TableName + '(' + cntCol2 + ')';
                        break;
                    case Language.VB:
                        strParam2 += (strParam2 == "" ? "" : ",\n\t\t\t\t\t" + getSpacing(TableName + "BO")) + "my" + TableName + '(' + cntCol2 + ')';
                        break;
                    default: break;
                }

                cntCol2++;
            }

            switch (Lang)
            {
                case Language.CS:
                    strCode += "\t\t" + "public " + TableName + "BO" + " get" + TableName + "Detail(" + strAgrument + ")" + "\n" +
                               "\t\t{\n" +
                               "\t\t\t" + "dynamic my" + TableName + " = my" + TableName + "DAL.SelectRow(" + strParam1 + ");\n" +
                               "\t\t\t" + "return new " + TableName + "BO(" + strParam2 + ");\n" +
                               "\t\t}\n\n";

                    strCode += "\t\t" + "public DataTable get" + TableName + "List(string range, List<object> rangeargs, List<object> @orderby)" + "\n" +
                               "\t\t{\n" +
                               "\t\t\t" + "return my" + TableName + "DAL.SelectTable(range, rangeargs, @orderby);" + "\n" +
                               "\t\t}\n\n";

                    strCode += "\t\t" + "public int Insert(" + TableName + "BO " + TableName + ")\n" +
                               "\t\t{\n" +
                               "\t\t\t" + "return my" + TableName + "DAL.Insert(" + TableName + ".ToObjectArray());" + "\n" +
                               "\t\t}\n\n";

                    strCode += "\t\t" + "public int Update(" + TableName + "BO " + TableName + ")\n" +
                               "\t\t{\n" +
                               "\t\t\t" + "return my" + TableName + "DAL.Update(" + TableName + ".ToObjectArray());" + "\n" +
                               "\t\t}\n\n";

                    strCode += "\t\t" + "public int Delete(" + strAgrument + ")\n" +
                               "\t\t{\n" +
                               "\t\t\t" + "return my" + TableName + "DAL.Delete(" + strParam1 + ");" + "\n" +
                               "\t\t}";
                    break;
                case Language.VB:
                    strCode += "\t" + "Public Function get" + TableName + "Detail(" + strAgrument + ") As " + TableName + "BO" + "\n" +
                               "\t\t" + "Dim my" + TableName + " = my" + TableName + "DAL.SelectRow(" + strParam1 + ")\n" +
                               "\t\t" + "Return New " + TableName + "BO(" + strParam2 + ")\n" +
                               "\tEnd Function" + "\n\n";

                    strCode += "\t" + "Public Function get" + TableName + "List(range as String, rangeargs As List(Of Object), orderby As List(Of Object)) As DataTable" + "\n" +
                               "\t\t" + "Return my" + TableName + "DAL.SelectTable(range, rangeargs, orderby)" + "\n" +
                               "\tEnd Function" + "\n\n";

                    strCode += "\t" + "Public Function Insert(" + TableName + " As " + TableName + "BO" + ") As Integer\n" +
                               "\t\t" + "Return my" + TableName + "DAL.Insert(" + TableName + ".ToObjectArray())" + "\n" +
                               "\tEnd Function" + "\n\n";

                    strCode += "\t" + "Public Function Update(" + TableName + " As " + TableName + "BO" + ") As Integer\n" +
                               "\t\t" + "Return my" + TableName + "DAL.Update(" + TableName + ".ToObjectArray())" + "\n" +
                               "\tEnd Function" + "\n\n";

                    strCode += "\t" + "Public Function Delete(" + strAgrument + ") As Integer\n" +
                               "\t\t" + "Return my" + TableName + "DAL.Delete(" + strParam1 + ")\n" +
                               "\tEnd Function";
                    break;
                default: break;
            }

            strClass = getClass(TableName + "BLL", Lang, strCode);

            strDirective = getDirective(Lang) + "\n\n";
            strNamespace = getNameSpace(myDbSchema.DatabaseName, Lang, strClass);

            strBLL = strDirective + strNamespace;
            return strBLL;
        }

        protected internal string getConstructor(string ClassName,string Params,Language Lang,string InnerCode)
        {
            string result = "";

            switch (Lang)
            {
                case Language.CS:
                    result += "\t\t" + "public " + ClassName + '(' + Params + ')' + "\n" +
                             "\t\t{\n" + 
                                InnerCode + "\n" + 
                             "\t\t}";
                    break;
                case Language.VB:
                    result += "\t" + "Public Sub New(" + Params + ')' +
                              "\t\t" + (InnerCode == "" ? "\n" : InnerCode) + "\n" +
                              "\tEnd Sub";
                    break;
                default: break;
            }

            return result;
        }

        protected internal string getCondition(Column myCol, Language Lang)
        {
            string result = "";
            result += myCol.Name + " = @s";
            return result;
        }

        protected internal string getParamString(Column myCol, Language Lang)
        {
            string strResult = "";
            
            switch (Lang)
            {
                case Language.CS:
                    strResult += DeriveCLSType(myCol).Name + " " + myCol.Name;
                    break;
                case Language.VB:
                    strResult += myCol.Name + " As " + DeriveCLSType(myCol).Name;
                    break;
                default: break;
            }

            return strResult;
        }

        protected internal string getDirective(Language Lang)
        {
            string result = "";

            switch (Lang)
            {
                case Language.CS:
                    result += "using System;\n" +
                              "using System.Collections.Generic;\n" +
                              "using System.Linq;\n" +
                              "using System.Text;\n" +
                              "using System.Data;\n" +
                              "using System.Data.SqlClient;\n" +
                              "using System.Collections;\n" + 
                              "using System.Configuration;\n" +
                              "using WINBASE.SQLHelper;";
                    break;
                case Language.VB:
                    result += "Imports System\n" +
                              "Imports System.Collections.Generic\n" +
                              "Imports System.Linq\n" +
                              "Imports System.Text\n" +
                              "Imports System.Data\n" +
                              "Imports System.Data.SqlClient\n" +
                              "Imports System.Collections\n" +
                              "Imports System.Configuration\n" +
                              "Imports WINBASE.SQLHelper";
                    break;
                default: break;
            }

            return result;
        }

        protected internal string getProperty(Column PropertyColumn,Language Lang)
        {
            string result = "";
            string strPropertyName = PropertyColumn.Name;
            string strDataType = DeriveCLSType(PropertyColumn).Name;

            switch (Lang)
            {
                case Language.CS:
                    result += "\t\tprivate " + strDataType + " _" + strPropertyName + ";\n" +
                            "\t\tpublic string " + strPropertyName + "\n" +
                            "\t\t{\n" +
                            "\t\t\tget { return _" + strPropertyName + "; }\n" +
                            "\t\t\tset { _" + strPropertyName + " = value; }\n" +
                            "\t\t}";
                    break;
                case Language.VB:
                    result += "\tPrivate _" + strPropertyName + " AS " + strDataType + "\n" +
                            "\tPublic Property " + strPropertyName + "\n" +
                            "\t\tGet \n" +
                            "\t\t\tReturn _" + strPropertyName + " \n" +
                            "\t\tEnd Get \n" +
                            "\t\tSet \n" +
                            "\t\t\t_" + strPropertyName + " = value \n" +
                            "\t\tEnd Set \n" +
                            "\tEnd Property";
                    break;
                default: result = ""; break;
            }

            return result;
        }

        protected internal string getClass(string ClassName,Language Lang,string InnerCode)
        {
            string result = "";

            switch (Lang)
            {
                case Language.CS:
                    result += "\tpublic class " + ClassName + "\n" +
                            "\t{\n" + InnerCode + "\n\t}";
                    break;
                case Language.VB:
                    result += "Public Class " + ClassName + "\n\n" +
                            InnerCode + "\n\nEnd Class";
                    break;
                default: result = ""; break;
            }

            return result;
        }

        protected internal string getNameSpace(string NameSpaceName, Language Lang,string InnerClass)
        {
            string result = "";

            switch (Lang)
            {
                case Language.CS:
                    result += "namespace " + NameSpaceName + "\n" +
                            "{\n" + 
                                InnerClass + "\n" +
                            "}";
                    break;
                case Language.VB:
                    result = InnerClass;
                    break;
                default: result = ""; break;
            }

            return result;
        }

        protected internal System.Type DeriveCLSType(Microsoft.SqlServer.Management.Smo.Column col)
        {
            Type myType = typeof(string);
            Microsoft.SqlServer.Management.Smo.SqlDataType ColType = col.DataType.SqlDataType;

            switch (ColType)
            {
                case Microsoft.SqlServer.Management.Smo.SqlDataType.BigInt:
                    myType = typeof(System.Int64);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Bit:
                    myType = typeof(System.Boolean);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Char:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.VarChar:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.VarCharMax:
                    myType = typeof(System.String);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.DateTime:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.SmallDateTime:
                    myType = typeof(System.DateTime);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Decimal:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Money:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.SmallMoney:
                    myType = typeof(System.Decimal);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Int:
                    myType = typeof(System.Int32);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.NChar:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.NText:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.NVarChar:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.NVarCharMax:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Text:
                    myType = typeof(System.String);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Real:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Numeric:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Float:
                    myType = typeof(System.Double);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Timestamp:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Binary:
                    myType = typeof(System.Byte);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.TinyInt:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.SmallInt:
                    myType = typeof(System.Int16);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.UniqueIdentifier:
                    myType = typeof(System.Guid);
                    break;
                case Microsoft.SqlServer.Management.Smo.SqlDataType.UserDefinedDataType:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.UserDefinedType:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Variant:
                case Microsoft.SqlServer.Management.Smo.SqlDataType.Image:
                    myType = typeof(Object);
                    break;
                default:
                    myType = typeof(System.String);
                    break;
            }

            return myType;
        }

        protected internal string getSpacing(string Str)
        {
            string result = "";
            int tapCnt = Str.Length / 4;
            int spaceCnt = Str.Length % 4;
            for (int t = 0; t < tapCnt; t++) result += "\t";
            for (int s = 0; s < spaceCnt; s++) result += ' ';
            return result;
        }
    }
}